package com.four.commodity.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.four.commodity.dao.CommodityDao;
import com.four.commodity.pojo.Commodity;
import com.four.commodity.util.DBUtil;

public class CommodityDaoImpl implements CommodityDao{

	@Override
	public List<Commodity> selectAll() {
		List<Commodity> list = new ArrayList<>();
		Connection conn = DBUtil.getConnection();
		String sql = "select * from conn where flag= 1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Commodity c = new Commodity();
				c.setId(rs.getInt("id"));
				c.setCategory_main_id(rs.getString("category_main_id"));
				c.setCategory_branch_id(rs.getString("category_branch_id"));
				c.setName(rs.getString("name"));
				c.setProducing_area(rs.getString("producing_area"));
				c.setCreate_time(rs.getString("create_time"));
				c.setMarket_price(rs.getString("market_price"));
				c.setSell_price(rs.getString("sell_price"));
				c.setProduct_amount(rs.getString("product_amount"));
				c.setPicture(rs.getString("picture"));
				c.setDiscount(rs.getString("discount"));
				c.setFlag(rs.getInt("flag"));
				list.add(c);
				c = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(conn, ps, rs);
		}
		return list;
	}
	
	public Commodity selectByName(String name) {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from conn where name = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		System.out.println("Dao - name:" + name);
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,name);
			rs = ps.executeQuery();
			if(rs.next()) {
				Commodity c = new Commodity();
				c.setId(rs.getInt("id"));
				c.setCategory_main_id(rs.getString("category_main_id"));
				c.setCategory_branch_id(rs.getString("category_branch_id"));
				c.setName(rs.getString("name"));
				c.setProducing_area(rs.getString("producing_area"));
				c.setCreate_time(rs.getString("create_time"));
				c.setMarket_price(rs.getString("market_price"));
				c.setSell_price(rs.getString("sell_price"));
				c.setProduct_amount(rs.getString("product_amount"));
				c.setPicture(rs.getString("picture"));
				c.setDiscount(rs.getString("discount"));
				c.setFlag(rs.getInt("flag"));
				return c;
				
			}
			
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public int addCommodity(int id, String name,String producing_area,String descrption,String create_time,String market_price,String product_amount,String picture,String discount,int flag) {
		Connection conn = DBUtil.getConnection();
		String sql = "INSERT INTO `conn`(`id`,`name`,`producing_area`,`descrption`,"
				+ "`create_time`,`market_price`,`product_amount`,`picture`,`discount`,`flag`)"
				+ " VALUES (?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,id);
			ps.setString(2, name);
			ps.setString(3, producing_area);
			ps.setString(4, descrption);
			ps.setString(5, create_time);
			ps.setString(6, market_price);
			ps.setString(7, product_amount);
			ps.setString(8, picture);
			ps.setString(9, discount);
			ps.setInt(10, flag);
			ps.setInt(11, 1);
			n = ps.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(conn, ps, null);
		}
		return n;
	}
	
	
	public int updateCommodity(int id,String name, String producing_area,String descrption,String create_time,String market_price,String product_amount,String picture,String discount,int flag) {
		Connection conn = DBUtil.getConnection();
		String sql = "UPDATE `conn` SET `name`=?,`producing_area`=?,"
				+ "`create_time`=?,`market_price`=?,`product_amount`=?,`picture`=?,`discount`=?,`flag`=? WHERE(`id`=?)";
		
		PreparedStatement ps =null;
		int n =0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, producing_area);
			ps.setString(3, create_time);
			ps.setString(4, market_price);
			ps.setString(5, product_amount);
			ps.setString(6, picture);
			ps.setString(7, discount);
			ps.setInt(8, flag);
			ps.setInt(9, id);
			n = ps.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(conn, ps, null);
		}
		return n;
	}
	
	public int deleteCommodity(int id) {
		Connection conn = DBUtil.getConnection();
		String sql = "update conn set flag=1 where id =?";
		PreparedStatement ps =null;
		int n = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			n = ps.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(conn, ps, null);
		}
		return n;
	}
	
	public Commodity selectById(int id) {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from conn where id = ?";
		PreparedStatement ps =null;
		ResultSet rs = null;
		System.out.println("Dao - name:  " + id);
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				Commodity c = new Commodity();
				c.setId(rs.getInt("id"));
				c.setCategory_main_id(rs.getString("category_main_id"));
				c.setCategory_branch_id(rs.getString("category_branch_id"));
				c.setName(rs.getString("name"));
				c.setProducing_area(rs.getString("producing_area"));
				c.setCreate_time(rs.getString("create_time"));
				c.setMarket_price(rs.getString("market_price"));
				c.setSell_price(rs.getString("sell_price"));
				c.setProduct_amount(rs.getString("product_amount"));
				c.setPicture(rs.getString("picture"));
				c.setDiscount(rs.getString("discount"));
				return c;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
